insert OVERWRITE table vip_oil
select A.carduser_id,A.station_id,A.transaction_id, substr(A.transaction_date,1,16),A.sale_total,B.product_code,B.item_quantity,B.item_price
from transactions_vip as A
join transaction_items_oil_vip as B 
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert OVERWRITE table vip_notoil
select A.carduser_id,A.station_id,A.transaction_id, substr(A.transaction_date,1,16),B.item_description,B.item_quantity,B.item_price,B.item_value,B.product_code,D.department_id
from transactions_vip as A
join transaction_items_notoil_vip as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
left join mid.products as C
on C.product_code = B.product_code and C.station_id = 50006
join mid.departments as D
on D.department_id = C.department_id and D.station_id = 50006
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert overwrite table vip_coupon
select A.carduser_id,A.station_id,A.transaction_id,substr(A.transaction_date,1,16),B.item_description,B.item_value
from transactions_vip as A
JOIN transaction_items_mop_vip AS B
on A.transaction_id = B.transaction_id and A.station_id = B.station_id and B.item_description like '%优惠%'
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert OVERWRITE table basket_bj
select A.*,B.oil_code,B.oil_quantity,B.oil_price
from vip_notoil as A
join vip_oil as B 
on A.station_id = B.station_id and A.transaction_id = B.transaction_id
and substr(A.transaction_date,1,10)=substr(B.transaction_date,1,10);


insert overwrite  table transactions_vip_cd
select B.CARDUSERID,A.*
from  mid.transactions as A
join mid.TBL_ORDERDETAILS_CD as B
on A.transaction_number = B.ORDERNUM and A.station_id = B.STATIONCODE and A.sale_total = B.AMOUNT/100 AND ORDER_DEAL = 4
and substr(A.transaction_date,1,10) = substr(B.SENDTIME,1,10);

INSERT OVERWRITE TABLE transaction_items_notoil_vip_cd
select A.carduser_id,B.*
from transactions_vip_cd as A
JOIN mid.transaction_items_notoil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

INSERT OVERWRITE TABLE transaction_items_oil_vip_cd
select A.carduser_id,B.*
from transactions_vip_cd as A
JOIN mid.transaction_items_oil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where B.item_type!=20 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert OVERWRITE table vip_oil_cd
select A.carduser_id,A.station_id,A.transaction_id, substr(A.transaction_date,1,16),A.sale_total,B.product_code,B.item_quantity,B.item_price
from transactions_vip_cd as A
join transaction_items_oil_vip_cd as B 
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert OVERWRITE table vip_notoil_cd
select A.carduser_id,A.station_id,A.transaction_id, substr(A.transaction_date,1,16),B.item_description,B.item_quantity,B.item_price,B.item_value,B.product_code,D.department_id
from transactions_vip_cd as A
join transaction_items_notoil_vip_cd as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
left join mid.products as C
on C.product_code = B.product_code and C.station_id = 50006
join mid.departments as D
on D.department_id = C.department_id and D.station_id = 50006
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert OVERWRITE table basket_cd
select A.*,B.oil_code,B.oil_quantity,B.oil_price
from vip_notoil_cd as A
join vip_oil_cd as B 
on A.station_id = B.station_id and A.transaction_id = B.transaction_id and substr(A.transaction_date,1,10)=substr(B.transaction_date,1,10);

insert OVERWRITE table vip_oil_all
select A.station_id,A.transaction_id, substr(A.transaction_date,1,16),A.sale_total,B.product_code,B.item_quantity,B.item_price
from mid.transactions as A
join mid.transaction_items_oil as B 
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
and substr(A.transaction_date,1,10)=substr(B.transaction_date,1,10)
where B.item_type!=20;

insert OVERWRITE table vip_notoil_all
select A.station_id,A.transaction_id, substr(A.transaction_date,1,16),B.item_description,B.item_quantity,B.item_price,B.item_value,B.product_code,D.department_id
from mid.transactions as A
join mid.transaction_items_notoil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id and substr(A.transaction_date,1,10)=substr(B.transaction_date,1,10)
left join mid.products as C
on C.product_code = B.product_code and C.station_id = 50006
join mid.departments as D
on D.department_id = C.department_id and D.station_id = 50006;

insert OVERWRITE table basket_all
select A.*,B.oil_code,B.oil_quantity,B.oil_price
from vip_notoil_all as A
join vip_oil_all as B 
on A.station_id = B.station_id and A.transaction_id = B.transaction_id and substr(A.transaction_date,1,10)=substr(B.transaction_date,1,10);



INSERT OVERWRITE TABLE mid_RFM
select A.CARDUSERID,A.MAX,A.MIN,B.THREECOUNT,B.THREERATE,B.THREEMONEY,A.HISTORYCOUNT,A.HISTORYRATE,A.HISTORYMONEY
from 
(select CARDUSERID,SUBSTR(MAX(SENDTIME),1,10)AS MAX,SUBSTR(MIN(SENDTIME),1,10)AS MIN,COUNT(CARDUSERID)AS HISTORYCOUNT,COUNT(CARDUSERID)/datediff(MAX(SENDTIME),MIN(SENDTIME))/7 AS HISTORYRATE,sum(AMOUNT)/100 AS HISTORYMONEY
from mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4
GROUP BY CARDUSERID) as A
full join 
(select CARDUSERID,count(CARDUSERID) AS THREECOUNT,count(CARDUSERID)/13 AS THREERATE,sum(AMOUNT)/100 AS THREEMONEY
from mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4 AND SENDTIME >date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID) as B
on A.CARDUSERID = B.CARDUSERID;

insert overwrite table RFM
select a.*,b.customer_level
from mid_RFM as a
left join TBL_CARDUSER AS b
on b.carduser_id = a.carduser_id;

INSERT OVERWRITE TABLE mid_RFM_cd
select A.CARDUSERID,A.MAX,A.MIN,B.THREECOUNT,B.THREERATE,B.THREEMONEY,A.HISTORYCOUNT,A.HISTORYRATE,A.HISTORYMONEY
from 
(select CARDUSERID,SUBSTR(MAX(SENDTIME),1,10)AS MAX,SUBSTR(MIN(SENDTIME),1,10)AS MIN,COUNT(CARDUSERID)AS HISTORYCOUNT,COUNT(CARDUSERID)/datediff(MAX(SENDTIME),MIN(SENDTIME))/7 AS HISTORYRATE,sum(AMOUNT)/100 AS HISTORYMONEY
from mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4
GROUP BY CARDUSERID) as A
full join 
(select CARDUSERID,count(CARDUSERID) AS THREECOUNT,count(CARDUSERID)/13 AS THREERATE,sum(AMOUNT)/100 AS THREEMONEY
from mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 AND SENDTIME >date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID) as B
on A.CARDUSERID = B.CARDUSERID;

insert overwrite table RFM_cd
select a.*,b.customer_level
from mid_RFM_cd as a
left join TBL_CARDUSER_CD AS b
on b.carduser_id = a.carduser_id;

insert overwrite table vip_item
select coalesce(a.carduser_id,b.carduser_id,c.carduser_id),coalesce(a.station_id,b.station_id,b.station_id),coalesce(a.transaction_id,b.transaction_id,c.transaction_id),a.transaction_date,a.oil_code,a.oil_quantity,a.oil_price,a.theory_amount,c.coupon_name,c.coupon_value,b.product_name,b.product_quantity,b.product_price,b.product_value,b.product_code,b.department_id
from vip_oil as a
full join vip_notoil as b
on b.transaction_id = a.transaction_id and b.station_id = a.station_id
and substr(a.transaction_date,1,10)=substr(b.transaction_date,1,10)
full join vip_coupon as c 
on c.transaction_id = a.transaction_id and c.station_id = a.station_id
and substr(a.transaction_date,1,10)=substr(c.transaction_date,1,10);

insert overwrite table vip_mid_interval_cd
select b.carduserid,CURRENT_DATE,
max(case when b.rank = 1 then b.sendtime else null end) as one,
max(case when b.rank = 2 then b.sendtime else null end) as two,
max(case when b.rank = 3 then b.sendtime else null end) as three,
max(case when b.rank = 4 then b.sendtime else null end) as four,
max(case when b.rank = 5 then b.sendtime else null end) as five,
max(case when b.rank = 6 then b.sendtime else null end) as six,
max(case when b.rank = 7 then b.sendtime else null end) as seven,
max(case when b.rank = 8 then b.sendtime else null end) as eight,
max(case when b.rank = 9 then b.sendtime else null end) as nine,
max(case when b.rank = 10 then b.sendtime else null end) as ten
from 
(select * from 
(select carduserid,sendtime,
row_number() over(partition by carduserid order by sendtime desc) as rank
from mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 AND LITRE > 0)as a
where a.rank < 11)as b group by b.carduserid;

insert overwrite table vip_mid_interval
select b.carduserid,CURRENT_DATE,
max(case when b.rank = 1 then b.sendtime else null end) as one,
max(case when b.rank = 2 then b.sendtime else null end) as two,
max(case when b.rank = 3 then b.sendtime else null end) as three,
max(case when b.rank = 4 then b.sendtime else null end) as four,
max(case when b.rank = 5 then b.sendtime else null end) as five,
max(case when b.rank = 6 then b.sendtime else null end) as six,
max(case when b.rank = 7 then b.sendtime else null end) as seven,
max(case when b.rank = 8 then b.sendtime else null end) as eight,
max(case when b.rank = 9 then b.sendtime else null end) as nine,
max(case when b.rank = 10 then b.sendtime else null end) as ten
from 
(select * from 
(select carduserid,sendtime,
row_number() over(partition by carduserid order by sendtime desc) as rank
from mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4 AND LITRE > 0)as a
where a.rank < 11)as b group by b.carduserid;


insert overwrite table vip_interval_cd
select carduser_id,
datediff(today,one),
datediff(one,two),
datediff(two,three),
datediff(three,four),
datediff(four,five),
datediff(five,six),
datediff(six,seven),
datediff(seven,eight),
datediff(eight,nine),
datediff(nine,ten)
from vip_mid_interval_cd;

insert overwrite table vip_interval
select carduser_id,
datediff(today,one),
datediff(one,two),
datediff(two,three),
datediff(three,four),
datediff(four,five),
datediff(five,six),
datediff(six,seven),
datediff(seven,eight),
datediff(eight,nine),
datediff(nine,ten)
from vip_mid_interval;
